package com.informet.ipd.modelo.dao;


import com.informet.ipd.modelo.vo.APermanenteVO;
import com.informet.ipd.modelo.vo.FisiMetaVO;
import com.informet.ipd.modelo.vo.MActividadVO;
import com.informet.ipd.modelo.vo.ProgEstrVO;
import com.informet.ipd.modelo.vo.PlanOperativoVO;
import com.informet.ipd.modelo.vo.TActividadVO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;



public class PlanOperativoDao  extends DBase{

	
	Connection conn = null;

    public PlanOperativoDao()  {
        super();
    }

    
    private void setAPermanente(APermanenteVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
       //bean.setTX_RESPONACTPER(rs.getString("TX_RESPONACTPER"));
        //bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));fg

    }
    
    public void  setMetaAPerm(MActividadVO bean, ResultSet rs) throws SQLException{
        bean.setTX_UMACTPER(rs.getString("TX_UMACTPER"));
        bean.setNUM_CANTACTPER(rs.getInt("NUM_CANTACTPER"));
        bean.setTX_TIPOACTPER(rs.getString("TX_TIPOACTPER"));
    }
    
    
    
    
    public void  setListPlanOperativo(PlanOperativoVO bean, ResultSet rs) throws SQLException{
    	bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setTX_UMACTPER(rs.getString("TX_UMACTPER"));
        bean.setNUM_CANTACTPER(rs.getInt("NUM_CANTACTPER"));
        bean.setTX_TIPOACTPER(rs.getString("TX_TIPOACTPER"));
        bean.setDT_INIACTPER(rs.getString("DT_INIACTPER"));
        bean.setDT_TERACTPER(rs.getString("DT_TERACTPER"));
        bean.setTOTAL(rs.getDouble("TOTAL"));
        bean.setNUM_CODMETAAPER_PK(rs.getInt("NUM_CODMETAAPER_PK"));

        //Ini agregado
        bean.setCodObj(rs.getString("NUM_CODOBJ_PK"));
        bean.setCodObjEsp(rs.getString("NUM_CODOBJESP_PK"));
        bean.setDescObj(rs.getString("descObj"));
        bean.setDescObjEsp(rs.getString("descObjEsp"));
 }

    public void  setListPlanOperativomdificado(PlanOperativoVO bean, ResultSet rs) throws SQLException{
    	bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setTX_UMACTPER(rs.getString("TX_UMACTPER"));
        bean.setNUM_CANTACTPER(rs.getInt("NUM_CANTACTPER"));
        bean.setTX_TIPOACTPER(rs.getString("TX_TIPOACTPER"));
        bean.setDT_INIACTPER(rs.getString("DT_INIACTPER"));
        bean.setDT_TERACTPER(rs.getString("DT_TERACTPER"));
        bean.setTOTAL(rs.getDouble("TOTAL"));
        bean.setNUM_CODMETAAPER_PK(rs.getInt("NUM_CODMETAAPER_PK"));
        bean.setNUM_ACTSUSPENSION(rs.getString("NUM_ACTSUSPENSION"));

         //Ini agregado
        bean.setCodObj(rs.getString("NUM_CODOBJ_PK"));
        bean.setCodObjEsp(rs.getString("NUM_CODOBJESP_PK"));
        bean.setDescObj(rs.getString("descObj"));
        bean.setDescObjEsp(rs.getString("descObjEsp"));
 }
    


    
    
    public List getlist() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<APermanenteVO> lista = new ArrayList<APermanenteVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODACTPER_PK,TX_DESCACTPER,TX_RESPONACTPER from ME_ACTPER";
            rs = stmt.executeQuery(sql);
            APermanenteVO bean = null;
            while (rs.next()) {
                bean = new APermanenteVO();
                setAPermanente(bean, rs);
                lista.add(bean);
                //JOptionPane.showMessageDialog(null, bean.getNUM_CODACTPER_PK());
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }
    
    
    
    
    

     
     public List getFiltro(int id) throws SQLException {
         PreparedStatement pstmt = null;
         Statement stmt = null;
         ResultSet rs = null;
         ArrayList<APermanenteVO> lista = new ArrayList<APermanenteVO>(0);
         //lista=null;
         String sql = "";
         try {
             conn = DriverManager.getConnection(url, usuario, password);
             sql = "select NUM_CODACTPER_PK from ME_ACTPER where NUM_CODOBJESP_PK=?";
             pstmt = conn.prepareStatement(sql);
             pstmt.setInt(1, id);
             rs = pstmt.executeQuery();
             APermanenteVO bean = null;
             while (rs.next()) {
                 bean = new APermanenteVO();
                 setAPermanente(bean, rs);
                 lista.add(bean);
             }
             rs.close();
             pstmt.close();
         } finally {
             if (rs != null) {
                 rs.close();
                 rs = null;
             }
             if (stmt != null) {
                 stmt.close();
                 stmt = null;
             }
             if (conn != null) {
                 conn.close();
                 conn = null;
             }

         }
         return lista;
     }
     
     
     
     
     public List getListPlanOperativo(int id,int id2)  throws SQLException {
    	 PreparedStatement pstmt = null;
         Statement stmt = null;
         ResultSet rs = null;
         ArrayList<PlanOperativoVO> lista = new ArrayList<PlanOperativoVO>(0);
         String sql = "";
         try {
             conn = DriverManager.getConnection(url, usuario, password);
             sql = "select ME_ACTPER.NUM_CODACTPER_PK,ME_ACTPER.TX_DESCACTPER, ME_ACTPER.NUM_PRESUACTPER as TOTAL,isnull(convert(varchar,ME_ACTPER.DT_INIACTPER,103),'(vacio)') as DT_INIACTPER,isnull(convert(varchar,ME_ACTPER.DT_TERACTPER,103),'(vacio)') as DT_TERACTPER, ME_METAAPER.NUM_CODMETAAPER_PK, isnull(ME_METAAPER.TX_UMACTPER,'') as TX_UMACTPER,isnull(ME_METAAPER.NUM_CANTACTPER,'') as NUM_CANTACTPER, isnull(ME_METAAPER.TX_TIPOACTPER,'') as TX_TIPOACTPER " +
                   //Ini Agragdo
                 ",  isnull((select tx_descobjesp from me_objesp o where o.NUM_CODOBJESP_PK=ME_ACTPER.NUM_CODOBJESP_PK),'') as descObjEsp "+
                 ",  NUM_CODOBJESP_PK, " +
                 "   isnull((select tx_descobj from me_obj o where o.NUM_CODOBj_pk=(select NUM_CODOBJ_PK from me_objesp o where o.NUM_CODOBJESP_PK=ME_ACTPER.NUM_CODOBJESP_PK)),'') as descObj "+
                 ",  isnull((select NUM_CODOBJ_PK from me_objesp o where o.NUM_CODOBJESP_PK=ME_ACTPER.NUM_CODOBJESP_PK),'-1') as NUM_CODOBJ_PK "+
                 //Fin Agragdo
                   " from ME_ACTPER inner join ME_METAAPER ON ME_METAAPER .NUM_CODACTPER_PK=ME_ACTPER .NUM_CODACTPER_PK full outer join ME_FINAMETA ON ME_ACTPER.NUM_CODACTPER_PK=ME_FINAMETA.NUM_CODACTPER_PK inner join ME_DEPENDENCIA on ME_DEPENDENCIA.NUM_CODDEPEN_PK=ME_ACTPER.NUM_CODDEPEN_PK " +
                   " where NUM_CODOBJESP_PK=? AND ME_DEPENDENCIA.NUM_CODDEPEN_PK=? order by NUM_CODACTPER_PK,NUM_CODMETAAPER_PK ";
             pstmt = conn.prepareStatement(sql);
             pstmt.setInt(1, id);
             pstmt.setInt(2, id2);
             rs = pstmt.executeQuery();
             PlanOperativoVO bean = null;
             while (rs.next()) {
                 bean = new PlanOperativoVO();
                 setListPlanOperativo(bean,rs);
                 lista.add(bean);
             }

         } finally {
             if (rs != null) {
                 rs.close();
                 rs = null;
             }
             if (stmt != null) {
                 stmt.close();
                 stmt = null;
             }
             if (conn != null) {
                 conn.close();
                 conn = null;
             }

         }
         return lista;
     }

     public List getListPlanOperativoModificado(int id,int id2)  throws SQLException {
    	 PreparedStatement pstmt = null;
         Statement stmt = null;
         ResultSet rs = null;
         ArrayList<PlanOperativoVO> lista = new ArrayList<PlanOperativoVO>(0);
         String sql = "";
         try {
             conn = DriverManager.getConnection(url, usuario, password);
             sql = " select ME_ACTPER.NUM_CODACTPER_PK,ME_ACTPER.NUM_ACTSUSPENSION,ME_ACTPER.TX_DESCACTPER,ME_ACTPER.NUM_PRESUACTPER as TOTAL, isnull(convert(varchar,ME_ACTPER.DT_INIACTPER,103),'(vacio)') as DT_INIACTPER, isnull(convert(varchar,ME_ACTPER.DT_TERACTPER,103),'(vacio)') as DT_TERACTPER, ME_METAAPER.NUM_CODMETAAPER_PK,isnull(ME_METAAPER.TX_UMACTPER,'') as TX_UMACTPER,isnull(ME_METAAPER.NUM_CANTACTPER,'') as NUM_CANTACTPER, isnull(ME_METAAPER.TX_TIPOACTPER,'') as TX_TIPOACTPER " +

                   //Ini Agragdo
                     ",  isnull((select tx_descobjesp from me_objesp o where o.NUM_CODOBJESP_PK=ME_ACTPER.NUM_CODOBJESP_PK),'') as descObjEsp "+
                     ",  NUM_CODOBJESP_PK, " +
                     "   isnull((select tx_descobj from me_obj o where o.NUM_CODOBj_pk=(select NUM_CODOBJ_PK from me_objesp o where o.NUM_CODOBJESP_PK=ME_ACTPER.NUM_CODOBJESP_PK)),'') as descObj "+
                     ",  isnull((select NUM_CODOBJ_PK from me_objesp o where o.NUM_CODOBJESP_PK=ME_ACTPER.NUM_CODOBJESP_PK),'-1') as NUM_CODOBJ_PK "+
                   //Fin Agragdo

                 " from ME_ACTPER inner join ME_METAAPER ON ME_METAAPER .NUM_CODACTPER_PK=ME_ACTPER .NUM_CODACTPER_PK full outer join ME_FINAMETAM ON ME_ACTPER.NUM_CODACTPER_PK=ME_FINAMETAM.NUM_CODACTPER_PK inner join ME_DEPENDENCIA on ME_DEPENDENCIA.NUM_CODDEPEN_PK=ME_ACTPER.NUM_CODDEPEN_PK " +
                 " where NUM_CODOBJESP_PK=? AND ME_DEPENDENCIA.NUM_CODDEPEN_PK=? order by NUM_CODACTPER_PK,NUM_CODMETAAPER_PK";
             pstmt = conn.prepareStatement(sql);
             pstmt.setInt(1, id);
             pstmt.setInt(2, id2);
             rs = pstmt.executeQuery();
             PlanOperativoVO bean = null;
             while (rs.next()) {
                 bean = new PlanOperativoVO();
                 setListPlanOperativomdificado(bean,rs);
                 lista.add(bean);
             }

         } finally {
             if (rs != null) {
                 rs.close();
                 rs = null;
             }
             if (stmt != null) {
                 stmt.close();
                 stmt = null;
             }
             if (conn != null) {
                 conn.close();
                 conn = null;
             }

         }
         return lista;
     }
	
     
     
     
     
     
     
     
     
     public String spMan(int tipo,PlanOperativoVO bean) {
         String msj = "";
         try {
        	 conn = DriverManager.getConnection(url, usuario, password);
             CallableStatement cs = conn.prepareCall("{call Man_ACTPERFECHA(?,?,?,?)}");
             cs.setInt(1, tipo);
             cs.setInt(2, bean.getNUM_CODACTPER_PK());
             cs.setString(3, bean.getDT_TERACTPER());
             cs.setString(4, bean.getDT_INIACTPER());
             cs.execute();
         } catch (Exception e) {
             msj = e.toString();
             return msj;
         }
         return msj;
         
     }


     public String spManM(int tipo,PlanOperativoVO bean) {
         String msj = "";
         try {
        	 conn = DriverManager.getConnection(url, usuario, password);
             CallableStatement cs = conn.prepareCall("{call Man_ACTPERFECHAD(?,?,?,?,?)}");
             cs.setInt(1, tipo);
             cs.setInt(2, bean.getNUM_CODACTPER_PK());
             cs.setString(3, bean.getDT_TERACTPER());
             cs.setString(4, bean.getDT_INIACTPER());
             cs.setString(5, bean.getNUM_ACTSUSPENSION());
             cs.execute();
         } catch (Exception e) {
             msj = e.toString();
             return msj;
         }
         return msj;

     }
	
}
